﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using GotDotNet.ApplicationBlocks.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OracleClient;

namespace LibraryUtility.OralceLib
{
    /// <summary>
    /// ClassName:Oracle Db Help.
    /// Auther:Leo.wl
    /// Time:2010-04-15
    /// </summary>
    public  class OraHelp
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="OraHelp"/> class.
        /// </summary>
        public OraHelp()
        {  
        }

        public AdoHelper GetAdoHelper()   //设置数据操作类
        {
            string assembly = null;
            string type = null;

            assembly = ConfigurationManager.AppSettings["OracleHelperAssembly"];
            type = ConfigurationManager.AppSettings["OracleHelperType"];

            return AdoHelper.CreateHelper(assembly, type);
        }


        public String GetConnectionString()   //返回连接
        {
            string connectionString = null;

            connectionString = ConfigurationManager.AppSettings["OracleConnectionString"];
            return connectionString;
        }




        public DataSet GetList(string fldStr, string strWhere, int PageIndex, int PageSize, string keyFld, string strSort, int isGetCount, string tableName)
        {
            AdoHelper helper = GetAdoHelper();
            string connString = GetConnectionString();



            IDataParameter[] parameters = {
											  new OracleParameter("@tab", OracleType.NVarChar, 255),
											  new OracleParameter("@strFld", OracleType.NVarChar, 500),
											  new OracleParameter("@strWhere",OracleType.NVarChar,500),
											  new OracleParameter("@Page", OracleType.Int16),
											  new OracleParameter("@RecsPerPage", OracleType.Int16),
											  new OracleParameter("@ID", OracleType.NVarChar,255),
											  new OracleParameter("@Sort", OracleType.NVarChar,255),
											  new OracleParameter("@isGetCount", OracleType.Int16),
			};

            parameters[0].Value = tableName;  //表名
            parameters[1].Value = fldStr;
            parameters[2].Value = strWhere;
            parameters[3].Value = PageIndex;
            parameters[4].Value = PageSize;
            parameters[5].Value = keyFld;
            parameters[6].Value = strSort;
            parameters[7].Value = isGetCount;

            //需要添加存储过程的名称
            return helper.ExecuteDataset(connString, CommandType.StoredProcedure, "", parameters);



        }

        /// <summary>
        /// Gets the list.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns></returns>
        public DataSet GetList(string sql)
        {
            AdoHelper helper = GetAdoHelper();
            string connString = GetConnectionString();
            return helper.ExecuteDataset(connString, CommandType.Text, sql);

        }


        /// <summary>
        /// Execs the SQL.
        /// </summary>
        /// <param name="strSql">The STR SQL.</param>
        /// <returns></returns>
        public int ExecSql(string strSql)
        {
            AdoHelper helper = GetAdoHelper();
            string connString = GetConnectionString();
            return helper.ExecuteNonQuery(connString, CommandType.Text, strSql);

        }


        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="strSql">The STR SQL.</param>
        /// <returns></returns>
        public string ExecuteScalar(string strSql)
        {
            AdoHelper helper = GetAdoHelper();
            string connString = GetConnectionString();

            try
            {
                string result = helper.ExecuteScalar(connString, CommandType.Text, strSql).ToString();
                return result;
            }
            catch (Exception ee)
            {
                return ee.ToString();
            }
            //  return ;



        }

        

        /// <summary>
        /// 在一个事务中执行多条SQL语句
        /// </summary>
        /// <param name="arrlistStr">sql语句的数组列表</param>
        /// <returns></returns>
        public int ExecAffair(System.Collections.ArrayList arrlistStr)//事务
        {
            AdoHelper helper = GetAdoHelper();

            int rel = 0;

            string myConnString = GetConnectionString();
            OracleConnection myConnection = new OracleConnection(myConnString);
            myConnection.Open();

            OracleCommand myCommand = myConnection.CreateCommand();
            OracleTransaction myTrans;

            myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            myCommand.Transaction = myTrans;

            try
            {

                for (int i = 0; i < arrlistStr.Count; i++)
                {
                    helper.ExecuteNonQuery(myTrans, CommandType.Text, arrlistStr[i].ToString());
                }
                //helper.ExecuteNonQuery(myTrans,CommandType.Text,sqlF);
                //helper.ExecuteNonQuery(myTrans,CommandType.Text,sqlT);
                myTrans.Commit();

                myConnection.Close(); ;

                rel = 1;

            }
            catch (SqlException ee)
            {
                myTrans.Rollback();
                myConnection.Close();
                throw ee;

            }
            finally
            {
                myConnection.Close();
            }

            return rel;

        }
    }
}
